<?php
class ItsReportsController extends AppController {
    var $name = 'ItsReports';

    var $pageTitle = 'ITS Report';

    var $viewPath = 'its/reports';

    var $uses = array('Dispatch', 'Product', 'CustProductSite', 'Customer', 'DispatchStatus', 'ItsStockInHead', 'Supplier');

    function beforeFilter() {
        parent::beforeFilter();
        $this->set('parentNav', 'its_reports');
        $this->set('tabMenu', 'tabmenus/its/reports');
    }

    function display() {
    }

    function _setDispatchOrderOptions() {

        // เงื่อนไขใน Order by
        $this->set('orderOptions', array(
                'Product.part_no' => 'Part no.',
                'SUM(CustStockIn.quotation_price)' => 'Price',
                'frequency' => 'Frequency',
                'stock_in_qty' => 'Quantity',
                '(CustStockIn.quotation_price * SUM(CustStockIn.stock_in_qty))' => 'Total Price',
                '(Product.last_dispatch_date)' => 'Dispatch Date',
        ));

        // เรียงจากน้อยไปมาก หรือ มากไปน้อย
        $this->set('seqOptions', array(
                'asc' => 'Ascendent',
                'desc' => 'Descendent'
        ));
    }

    /**
     * หน้าหลัก report Customer Stock out
     */
    function dispatch_search() {
        $this->viewPath = 'its/reports/dispatch';
        $this->pageTitle = 'ITS Dispatch Report';

        $this->set('customers', $this->Customer->find('list', array('order' => 'name')));
        $this->set('categories', $this->CustProductSite->Product->Category->find('list', array('order' => 'name')));

        $this->set('suppliers', $this->Product->Supplier->find('list', array('order' => 'name')));

        $this->_setDispatchOrderOptions();
    }

    /**
     * function ประมวลผลเงื่อนไขการค้นหา Customer stock out
     */
    function dispatch_query() {
        $this->viewPath = 'its/reports/dispatch';

        $sql = "SELECT Customer.cust_no, Customer.name, Site.site_no, Site.name,
                       CustProductSite.id, CustProductSite.stock_code,
                       Product.id, Product.part_no, Product.supplier_part_no, Product.text,
                       AVG(CustStockIn.quotation_price) price, SUM(CustStockIn.stock_in_qty) stock_in_qty, COUNT(*) frequency
                FROM   pre_products Product
                JOIN   pre_cust_stocks_in CustStockIn ON (Product.id = CustStockIn.product_id AND (CustStockIn.dispatch_status_id = " . DispatchStatus::CONSIGNED_STATUS ." OR CustStockIn.dispatch_status_id = " . DispatchStatus::PARTIAL_CONSIGNED_STATUS . "))
                JOIN   pre_cust_products_sites CustProductSite ON (CustStockIn.cust_product_site_id = CustProductSite.id)
                JOIN   pre_dispatches Dispatch ON (CustStockIn.dispatch_id = Dispatch.id)
                JOIN   pre_customers Customer ON (Customer.id = Dispatch.customer_id)
                JOIN   pre_sites Site ON (Site.id = Dispatch.site_id)
                WHERE  1 ";

        // เก็บ คำสั่ง SQL เอาไว้เป็นเงื่อนไขตอนดึง Stock out no หรือ remark
        $sql_conditions = '';
        // Check เงื่อนไขการค้นหา
        if (!empty($this->data['Product']['part_no'])) {
            $part_no = trim(strtoupper($this->data['Product']['part_no']));
            $str = " AND Product.part_no LIKE '%{$part_no}%'";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['category_id'])) {
            $str = " AND Product.category_id = {$this->data['Product']['category_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['supplier_id'])) {
            $str = " AND Product.supplier_id = {$this->data['Product']['supplier_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['brand_id'])) {
            $str = " AND Product.brand_id = {$this->data['Product']['brand_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Dispatch']['dispatch_date_start'])) {
            $str = " AND Dispatch.dispatch_date BETWEEN '{$this->data['Dispatch']['dispatch_date_start']}' AND '{$this->data['Dispatch']['dispatch_date_end']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['Dispatch']['customer_id'])) {
            $str = " AND Dispatch.customer_id = '{$this->data['Dispatch']['customer_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['Dispatch']['site_id'])) {
            $str = " AND Dispatch.site_id = '{$this->data['Dispatch']['site_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['Dispatch']['supplier_id'])) {
            $str = " AND Dispatch.supplier_id = '{$this->data['Dispatch']['supplier_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        $order1 = !empty($this->data['Option']['order1'])? $this->data['Option']['order1']: 'Product.part_no';
        $order2 = !empty($this->data['Option']['order2'])? $this->data['Option']['order2']: null;
        $seq = !empty($this->data['Option']['seq'])? $this->data['Option']['seq']: 'ASC';

        $sql .= " GROUP BY Product.part_no, CustProductSite.stock_code, CustStockIn.quotation_price ORDER BY {$order1} {$seq}" . ($order2 != null? ", {$order2} {$seq}": '');

        if (!empty($this->data['Option']['limit'])) {
            $sql .= ' LIMIT ' . $this->data['Option']['limit'];
        }

        // sql ที่จะใช้ดึงรายการ stock out no และ remark
        $this->Session->write($this->name . 'sql', $sql_conditions);
        // set ตัวแปรให้หน้า view ว่าต้องโชว์ stock out no กับ remark หรือไม่
        $this->set('display_dispatch_no', $this->data['Option']['display_dispatch_no']);

        $this->data = $this->Dispatch->query($sql);
    }

    function dispatch_print() {
        $this->viewPath = 'its/reports/dispatch';
        // ถ้ามีการค้นหาตาม Category ดึงชื่อ Category มาใส่ตัวแปรให้หน้า view
        if (!empty($this->data['Product']['category_id'])) {
            $this->Product->Category->id = $this->data['Product']['category_id'];
            $this->set('category_name', $this->Product->Category->field('name'));
        }

        if (!empty($this->data['Product']['supplier_id'])) {
            $this->Product->Supplier->id = $this->data['Product']['supplier_id'];
            $this->set('vendor_name', $this->Product->Supplier->field('name'));
        }

        if (!empty($this->data['Product']['brand_id'])) {
            $this->Product->Brand->id = $this->data['Product']['brand_id'];
            $this->set('brand_name', $this->Product->Brand->field('name'));
        }

        if (!empty($this->data['Dispatch']['customer_id'])) {
            $this->Dispatch->Customer->id = $this->data['Dispatch']['customer_id'];
            $this->set('customer_name', $this->Dispatch->Customer->field('name'));
        }

        if (!empty($this->data['Dispatch']['site_id'])) {
            $this->Dispatch->Site->id = $this->data['Dispatch']['site_id'];
            $this->set('site_name', $this->Dispatch->Site->field('name'));
        }

        $this->_setReportPrintNote();
        $this->_setDispatchOrderOptions();

        $this->dispatch_query();
    }

    /**
     * สำหรับ Stock out report กรณีมีการแสดง stock out no
     */
    function get_dispatch_no($product_id, $cust_product_site_id) {
        $site_id = $this->Auth->user('site_id');
        $sql = "SELECT Dispatch.id, dispatch_no, stock_in_qty, DATE_FORMAT(dispatch_date, '%d %M %Y') dispatch_date
                FROM   pre_products Product
                JOIN   pre_cust_stocks_in CustStockIn ON (Product.id = CustStockIn.product_id AND (CustStockIn.dispatch_status_id = " . DispatchStatus::CONSIGNED_STATUS ." OR CustStockIn.dispatch_status_id = " . DispatchStatus::PARTIAL_CONSIGNED_STATUS . "))
                JOIN   pre_dispatches Dispatch ON (CustStockIn.dispatch_id = Dispatch.id)
                WHERE  Product.id = {$product_id}
                AND    CustStockIn.cust_product_site_id = {$cust_product_site_id} ";

        // เอาเงื่อนไขจากการ search มาใส่เพื่ม
        $sql_condition = $this->Session->read($this->name . 'sql');
        $sql .= $sql_condition;

        $data = $this->CustProductSite->query($sql);
        return $data;
    }

    /**
     * กำหนดค่าตัวแปรให้แก่หน้า view
     */
    function _setReportPrintNote() {
        $this->layout = 'print';

        $this->set('report_date', date('d/m/Y'));
        $this->set('user', $this->Auth->user('name'));

        // เก็บเงื่อนไขที่ส่งมาลงตัวแปรให้หน้า view ตรวจสอบ
        $this->set('criteria', $this->data);
        $this->set('print_orientation', PRINT_LANDSCAPE);
    }

    function _setStockInOrderOptions() {

        // เงื่อนไขใน Order by
        $this->set('orderOptions', array(
                'Product.part_no' => 'Part no.',
                'SUM(ItsStockIn.cost)' => 'Cost',
                'frequency' => 'Frequency',
                'stock_in_qty' => 'Quantity',
                '(ItsStockIn.cost * SUM(ItsStockIn.stock_in_qty))' => 'Total Cost',
                '(Product.last_stock_in_date)' => 'Stock in Date',
        ));

        // เรียงจากน้อยไปมาก หรือ มากไปน้อย
        $this->set('seqOptions', array(
                'asc' => 'Ascendent',
                'desc' => 'Descendent'
        ));
    }

    /**
     * หน้าหลัก report Customer Stock out
     */
    function stock_in_search() {
        $this->viewPath = 'its/reports/stock_in';
        $this->pageTitle = 'ITS Stock in Report';

        $this->set('categories', $this->Product->Category->find('list', array('order' => 'name')));
        $this->set('suppliers', $this->Product->Supplier->find('list', array('order' => 'name')));

        $this->_setStockInOrderOptions();
    }

    function dispatch_excel() {
        $this->dispatch_print();
        parent::excel('VMI ITS Dispatch Report');
    }

    /**
     * function ประมวลผลเงื่อนไขการค้นหา Customer stock out
     */
    function stock_in_query() {
        $this->viewPath = 'its/reports/stock_in';

        $sql = "SELECT Product.id, Product.part_no, Product.text, Supplier.id, Supplier.name,
                       AVG(ItsStockIn.cost) cost, SUM(ItsStockIn.stock_in_qty) stock_in_qty, COUNT(*) frequency
                FROM   pre_products Product
                JOIN   pre_suppliers Supplier ON (Product.supplier_id = Supplier.id)
                JOIN   pre_its_stocks_in ItsStockIn ON (Product.id = ItsStockIn.product_id)
                JOIN   pre_its_stock_in_heads ItsStockInHead ON (ItsStockIn.its_stock_in_head_id = ItsStockInHead.id)
                WHERE  1 ";

        // เก็บ คำสั่ง SQL เอาไว้เป็นเงื่อนไขตอนดึง Stock out no หรือ remark
        $sql_conditions = '';
        // Check เงื่อนไขการค้นหา
        if (!empty($this->data['Product']['part_no'])) {
            $part_no = trim(strtoupper($this->data['Product']['part_no']));
            $str = " AND Product.part_no LIKE '%{$part_no}%'";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['category_id'])) {
            $str = " AND Product.category_id = {$this->data['Product']['category_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['supplier_id'])) {
            $str = " AND Product.supplier_id = {$this->data['Product']['supplier_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['brand_id'])) {
            $str = " AND Product.brand_id = {$this->data['Product']['brand_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['ItsStockInHead']['stock_in_date_start'])) {
            $str = " AND ItsStockInHead.stock_in_date BETWEEN '{$this->data['ItsStockInHead']['stock_in_date_start']}' AND '{$this->data['ItsStockInHead']['stock_in_date_end']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['ItsStockInHead']['customer_id'])) {
            $str = " AND ItsStockInHead.customer_id = '{$this->data['ItsStockInHead']['customer_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['ItsStockInHead']['site_id'])) {
            $str = " AND ItsStockInHead.site_id = '{$this->data['ItsStockInHead']['site_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['ItsStockInHead']['supplier_id'])) {
            $str = " AND ItsStockInHead.supplier_id = '{$this->data['ItsStockInHead']['supplier_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        $order1 = !empty($this->data['Option']['order1'])? $this->data['Option']['order1']: 'Product.part_no';
        $order2 = !empty($this->data['Option']['order2'])? $this->data['Option']['order2']: null;
        $seq = !empty($this->data['Option']['seq'])? $this->data['Option']['seq']: 'ASC';

        $sql .= " GROUP BY Product.part_no, ItsStockIn.cost ORDER BY {$order1} {$seq}" . ($order2 != null? ", {$order2} {$seq}": '');

        if (!empty($this->data['Option']['limit'])) {
            $sql .= ' LIMIT ' . $this->data['Option']['limit'];
        }

        // sql ที่จะใช้ดึงรายการ stock out no และ remark
        $this->Session->write($this->name . 'sql', $sql_conditions);

        // set ตัวแปรให้หน้า view ว่าต้องโชว์ stock out no กับ remark หรือไม่
        $this->set('display_stock_in_no', $this->data['Option']['display_stock_in_no']);

        $this->data = $this->ItsStockInHead->query($sql);
    }

    function stock_in_print() {
        $this->viewPath = 'its/reports/stock_in';
        // ถ้ามีการค้นหาตาม Category ดึงชื่อ Category มาใส่ตัวแปรให้หน้า view
        if (!empty($this->data['Product']['category_id'])) {
            $this->Product->Category->id = $this->data['Product']['category_id'];
            $this->set('category_name', $this->Product->Category->field('name'));
        }

        if (!empty($this->data['Product']['supplier_id'])) {
            $this->Product->Supplier->id = $this->data['Product']['supplier_id'];
            $this->set('vendor_name', $this->Product->Supplier->field('name'));
        }

        if (!empty($this->data['Product']['brand_id'])) {
            $this->Product->Brand->id = $this->data['Product']['brand_id'];
            $this->set('brand_name', $this->Product->Brand->field('name'));
        }

        $this->_setReportPrintNote();
        $this->_setStockInOrderOptions();
        $this->stock_in_query();
    }

    /**
     * สำหรับ Stock out report กรณีมีการแสดง stock out no
     */
    function get_stock_in_no($product_id) {
        $site_id = $this->Auth->user('site_id');
        $sql = "SELECT ItsStockInHead.id, ItsStockInHead.stock_in_no, stock_in_qty, DATE_FORMAT(stock_in_date, '%d %M %Y') stock_in_date
                FROM   pre_products Product
                JOIN   pre_its_stocks_in ItsStockIn ON (Product.id = ItsStockIn.product_id)
                JOIN   pre_its_stock_in_heads ItsStockInHead ON (ItsStockIn.its_stock_in_head_id = ItsStockInHead.id)
                WHERE  Product.id = {$product_id} ";

        // เอาเงื่อนไขจากการ search มาใส่เพื่ม
        $sql_condition = $this->Session->read($this->name . 'sql');
        $sql .= $sql_condition;

        $data = $this->CustProductSite->query($sql);
        return $data;
    }

    function stock_in_excel() {
        $this->stock_in_print();
        parent::excel('VMI ITS Stock In Report');
    }

    /**
     * หน้าหลัก report ITS Movement Inventory
     * จำนวน Current stock ปัจจุบัน
     */
    function movement_inventory_search() {
        $this->viewPath = 'its/reports/movement_inventory';
        $this->pageTitle = 'Movement Inventory Report';

        $this->set('categories', $this->CustProductSite->Product->Category->find('list', array('order' => 'name')));
        $this->set('suppliers', $this->Product->Supplier->find('list', array('order' => 'name')));
        $this->set('types', $this->Product->ItsType->find('list', array('field', array('type.id', 'type.code'), 'order' => 'name')));
        $this->set('operands', array('<' => '<', '<=' => '<=', '=' => '=', '>' => '>', '>=' => '>='));

        $this->_setDispatchOrderOptions();
    }

    function customer_reports() {
        $this->viewPath = 'its/reports/customer_reports';
        $this->pageTitle = 'Customer Repoert';
//        $this->set('customers', $this->Customer->find('list', array('order' => 'name')));
    }

    function cust_stock_in_search() {
        $this->viewPath = 'its/reports/customer_reports/stock_in';
        $this->pageTitle = 'Customer Stock in Report';

//        // ถ้าเป็น Vendor เข้ามาดุเห็นเฉพาะ brand ของตัวเอง
//        if ($this->Auth->user('prefix') != SUPPLIER_PREFIX) {
//            $suppliers = $this->CustProductSite->listSupplierByCustomerId($this->Auth->user('customer_id'));
//        } else {
//            $suppliers = $this->Auth->user('supplier_id');
//        }

        $this->set('categories', $this->CustProductSite->Product->Category->find('list', array('order' => 'name')));
        $this->set('brands', $this->Product->Brand->find('list', array('order' => 'name')));
        $this->set('suppliers', $this->Product->Supplier->find('list', array('order' => 'name')));
        $this->set('customers', $this->Customer->find('list', array('order' => 'name')));

        $this->setCustStockInOrderOptions();
    }

    function setCustStockInOrderOptions() {
        // เงื่อนไขใน Order by
        $this->set('orderOptions', array(
                'Product.part_no' => 'Part No.',
                'CustProductSite.stock_code' => 'Stock Code',
                'Customer.name' => 'Customer',
                'Site.name' => 'Site',
                'Supplier.name' => 'Vendor',
                'CustStockIn.quotation_price' => 'Price',
                'frequency' => 'Frequency',
                'stock_in_qty' => 'Quantity',
                '(CustStockIn.quotation_price * SUM(stock_in_qty))' => 'Total Price',
                '(CustProductSite.last_stock_in_date)' => 'Stock in Date',
        ));

        // เรียงจากน้อยไปมาก หรือ มากไปน้อย
        $this->set('seqOptions', array(
                'asc' => 'Ascendent',
                'desc' => 'Descendent'
        ));
    }

    /**
     * function ประมวลผลเงื่อนไขการค้นหา Customer stock in
     */
    function cust_stock_in_query() {
        $this->viewPath = 'its/reports/customer_reports/stock_in';

        $limit = $this->data['Option']['limit'];

        $sql = "SELECT Product.id, CustProductSite.id, CustProductSite.stock_code, CustProductSite.dispatch_from, CustStockInHead.dispatch_from, Product.part_no, Product.supplier_part_no, Supplier.name,
                       AVG(CustStockIn.quotation_price) price, SUM(stock_in_qty) stock_in_qty, COUNT(*) frequency,
                       Category.name, COALESCE(CustProductSite.text, Product.text) text,
                       Customer.name, Site.name
                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_categories Category ON (Category.id = Product.category_id)
                JOIN pre_cust_stocks_in CustStockIn ON (CustStockIn.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_in_heads CustStockInHead ON (CustStockIn.cust_stock_in_head_id = CustStockInHead.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                JOIN pre_customers Customer ON (Customer.id = CustProductSite.customer_id)
                JOIN pre_sites Site ON (Site.id = CustProductSite.site_id)
                WHERE CustProductSite.discontinued = 0 ";

        // เก็บ คำสั่ง SQL เอาไว้เป็นเงื่อนไขตอนดึง Stock in no
        $sql_conditions = '';
        // Check เงื่อนไขการค้นหา
        if (!empty($this->data['Product']['part_no'])) {
            $part_no = trim(strtoupper($this->data['Product']['part_no']));
            $str = " AND Product.part_no LIKE '%{$part_no}%'";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['category_id'])) {
            $str = " AND Product.category_id = {$this->data['Product']['category_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['brand_id'])) {
            $str = " AND Product.brand_id = {$this->data['Product']['brand_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustProductSite']['stock_code'])) {
            $stock_code = trim(strtoupper($this->data['CustProductSite']['stock_code']));
            $str = " AND CustProductSite.stock_code LIKE '%{$stock_code}%'";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustProductSite']['customer_id'])) {
            $customer_id = $this->data['CustProductSite']['customer_id'];
            $str = " AND CustProductSite.customer_id = '{$customer_id}'";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustProductSite']['site_id'])) {
            $site_id = $this->data['CustProductSite']['site_id'];
            $str = " AND CustProductSite.site_id = '{$site_id}'";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustStockInHead']['stock_in_date_start'])) {
            $str = " AND CustStockInHead.stock_in_date BETWEEN '{$this->data['CustStockInHead']['stock_in_date_start']}' AND '{$this->data['CustStockInHead']['stock_in_date_end']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustStockInHead']['dispatch_from'])) {
            $str = " AND CustStockInHead.dispatch_from = '{$this->data['CustStockInHead']['dispatch_from']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustStockInHead']['supplier_id'])) {
            $str = " AND CustStockInHead.supplier_id = '{$this->data['CustStockInHead']['supplier_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        $order1 = !empty($this->data['Option']['order1'])? $this->data['Option']['order1']: 'CustProductSite.stock_code';
        $order2 = !empty($this->data['Option']['order2'])? $this->data['Option']['order2']: 'CustProductSite.stock_code';
        $seq = !empty($this->data['Option']['seq'])? $this->data['Option']['seq']: 'ASC';

        $sql .= " GROUP BY CustProductSite.stock_code, CustStockIn.quotation_price ORDER BY {$order1} {$seq}" . ($order2 != null? ", {$order2} {$seq}": '');

        if (!empty($limit)) {
            $sql .= ' LIMIT ' . $limit;
        }

//        debug($sql);
        // sql ที่จะใช้ดึงรายการ stock out no
        $this->Session->write($this->name . 'sql', $sql_conditions);
        // set ตัวแปรให้หน้า view ว่าต้องโชว์ stock out no หรือไม่
        $this->set('display_stock_in_no', $this->data['Option']['display_stock_in_no']);
        $this->data = $this->CustProductSite->query($sql);
    }

    /**
     * สำหรับ Stock out report กรณีมีการแสดง stock out no
     */
    function get_cust_stock_in_no($cust_product_site_id, $cust_stock_in_price) {
        $sql = "SELECT CustStockInHead.id, stock_in_no, stock_in_qty, DATE_FORMAT(stock_in_date, '%d %M %Y') stock_in_date
                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_categories Category ON (Category.id = Product.category_id)
                JOIN pre_cust_stocks_in CustStockIn ON (CustStockIn.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_in_heads CustStockInHead ON (CustStockIn.cust_stock_in_head_id = CustStockInHead.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                WHERE CustStockIn.quotation_price = {$cust_stock_in_price}
                AND CustProductSite.id = {$cust_product_site_id} ";

        // เอาเงื่อนไขจากการ search มาใส่เพื่ม
        $sql_condition = $this->Session->read($this->name . 'sql');
        $sql .= $sql_condition;
//        debug($sql);
        $data = $this->CustProductSite->query($sql);
//        debug($data);
        return $data;
    }

    function cust_stock_in_print() {
        $this->viewPath = 'its/reports/customer_reports/stock_in';
        // ถ้ามีการค้นหาตาม Category ดึงชื่อ Category มาใส่ตัวแปรให้หน้า view
        if (!empty($this->data['Product']['category_id'])) {
            $this->CustProductSite->Product->Category->id = $this->data['Product']['category_id'];
            $this->set('category_name', $this->CustProductSite->Product->Category->field('name'));
        }

        if (!empty($this->data['CustProductSite']['customer_id'])) {
            $this->CustProductSite->Customer->id = $this->data['CustProductSite']['customer_id'];
            $this->set('customer_name', $this->CustProductSite->Customer->field('name'));
        }

        if (!empty($this->data['CustProductSite']['site_id'])) {
            $this->CustProductSite->Site->id = $this->data['CustProductSite']['site_id'];
            $this->set('site_name', $this->CustProductSite->Site->field('name'));
        }

        if (!empty($this->data['CustProductSite']['supplier_id'])) {
            $this->CustProductSite->Supplier->id = $this->data['CustProductSite']['supplier_id'];
            $this->set('supplier_name', $this->CustProductSite->Supplier->field('name'));
        }

        if (!empty($this->data['Product']['brand_id'])) {
            $this->CustProductSite->Product->Brand->id = $this->data['Product']['brand_id'];
            $this->set('brand_name', $this->CustProductSite->Product->Brand->field('name'));
        }

        if (!empty($this->data['CustStockInHead']['dispatch_from'])) {
            if ($this->data['CustStockInHead']['dispatch_from'] == ITS_PREFIX) {
                $this->set('vendor', $this->requestAction('system_preferences/get/company_name'));
            } else {
                if ($this->data['CustStockInHead']['supplier_id'] != null) {
                    $this->Supplier->id = $this->data['CustStockInHead']['supplier_id'];
                    $this->set('vendor', $this->Supplier->field('name'));
                } else {
                    $this->set('vendor', 'Other Vendor');
                }
            }
        }

        $this->setCustStockInOrderOptions();
        $this->_setReportPrintNote();
        $this->cust_stock_in_query();
    }

    function cust_stock_in_excel() {
        $this->cust_stock_in_print();
        parent::excel('VMI ITS Customer Stock In Report');
    }


    /**
     * หน้าหลัก report Customer Stock out
     */
    function cust_stock_out_search() {
        $this->viewPath = 'its/reports/customer_reports/stock_out';
        $this->pageTitle = 'Customer Stock out Report';
        $this->set('categories', $this->CustProductSite->Product->Category->find('list', array('order' => 'name')));
//
//        // ถ้าเป็น Vendor เข้ามาดุเห็นเฉพาะ brand ของตัวเอง
//        if ($this->Auth->user('prefix') != SUPPLIER_PREFIX) {
//            $suppliers = $this->CustProductSite->listSupplierByCustomerId($this->Auth->user('customer_id'));
//        } else {
//            $suppliers = $this->Auth->user('supplier_id');
//        }
        $this->set('categories', $this->CustProductSite->Product->Category->find('list', array('order' => 'name')));
        $this->set('brands', $this->Product->Brand->find('list', array('order' => 'name')));
        $this->set('suppliers', $this->Product->Supplier->find('list', array('order' => 'name')));
        $this->set('customers', $this->Customer->find('list', array('order' => 'name')));
        $this->setCustStockOutOrderOptions();
    }

    function setCustStockOutOrderOptions() {
        // เงื่อนไขใน Order by
        $this->set('orderOptions', array(
                'Product.part_no' => 'Part No.',
                'CustProductSite.stock_code' => 'Stock Code',
                'Customer.name' => 'Customer',
                'Site.name' => 'Site',
                'Supplier.name' => 'Vendor',
                'CustStockOut.stock_out_avg_price' => 'Price',
                'frequency' => 'Frequency',
                'stock_out_qty' => 'Quantity',
                '(CustStockOut.stock_out_avg_price * SUM(stock_out_qty))' => 'Total Price',
                '(CustProductSite.last_stock_out_date)' => 'Stock out Date',
        ));

        // เรียงจากน้อยไปมาก หรือ มากไปน้อย
        $this->set('seqOptions', array(
                'asc' => 'Ascendent',
                'desc' => 'Descendent'
        ));
    }

    /**
     * function ประมวลผลเงื่อนไขการค้นหา Customer stock out
     */
    function cust_stock_out_query() {
        $this->viewPath = 'its/reports/customer_reports/stock_out';
//        debug($this->data);

        $limit = $this->data['Option']['limit'];
        $site_id = $this->Auth->user('site_id');

        $sql = "SELECT Product.id, CustProductSite.id, CustProductSite.stock_code, CustProductSite.dispatch_from,
                       CustStockOutHead.dispatch_from,
                       Product.part_no, Product.supplier_part_no, Supplier.name, Product.text, CustProductSite.text,
                       COALESCE(CustProductSite.text, Product.text) text,
                       AVG(CustStockOut.stock_out_avg_price) price, SUM(stock_out_qty) stock_out_qty, SUM(return_qty) return_qty, SUM(net_stock_out_qty) net_stock_out_qty, COUNT(*) frequency, 
                       Customer.name, Site.name
                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                JOIN pre_categories Category ON (Category.id = Product.category_id)
                JOIN pre_cust_stocks_out CustStockOut ON (CustStockOut.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_out_heads CustStockOutHead ON (CustStockOut.cust_stock_out_head_id = CustStockOutHead.id)
                JOIN pre_customers Customer ON (Customer.id = CustProductSite.customer_id)
                JOIN pre_sites Site ON (Site.id = CustProductSite.site_id)
                WHERE CustProductSite.discontinued = 0 ";
//                AND CustStockOut.net_stock_out_qty > 0 ";

        // เก็บ คำสั่ง SQL เอาไว้เป็นเงื่อนไขตอนดึง Stock out no หรือ remark
        $sql_conditions = '';
        // Check เงื่อนไขการค้นหา
        if (!empty($this->data['Product']['part_no'])) {
            $part_no = trim(strtoupper($this->data['Product']['part_no']));
            $str = " AND Product.part_no LIKE '%{$part_no}%'";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['category_id'])) {
            $str = " AND Product.category_id = {$this->data['Product']['category_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }
        if (!empty($this->data['Product']['brand_id'])) {
            $str = " AND Product.brand_id = {$this->data['Product']['brand_id']}";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustProductSite']['stock_code'])) {
            $stock_code = trim(strtoupper($this->data['CustProductSite']['stock_code']));
            $str = " AND CustProductSite.stock_code LIKE '%{$stock_code}%'";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustProductSite']['customer_id'])) {
            $customer_id = $this->data['CustProductSite']['customer_id'];
            $str = " AND CustProductSite.customer_id = '{$customer_id}'";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustProductSite']['site_id'])) {
            $site_id = $this->data['CustProductSite']['site_id'];
            $str = " AND CustProductSite.site_id = '{$site_id}'";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustStockOutHead']['stock_out_date_start'])) {
            $str = " AND CustStockOutHead.stock_out_date BETWEEN '{$this->data['CustStockOutHead']['stock_out_date_start']}' AND '{$this->data['CustStockOutHead']['stock_out_date_end']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustStockOutHead']['dispatch_from'])) {
            $str = " AND CustStockOutHead.dispatch_from = '{$this->data['CustStockOutHead']['dispatch_from']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        if (!empty($this->data['CustStockOutHead']['supplier_id'])) {
            $str = " AND CustStockOutHead.supplier_id = '{$this->data['CustStockOutHead']['supplier_id']}' ";
            $sql .= $str;
            $sql_conditions .= $str;
        }

        $order1 = !empty($this->data['Option']['order1'])? $this->data['Option']['order1']: 'CustProductSite.stock_code';
        $order2 = !empty($this->data['Option']['order2'])? $this->data['Option']['order2']: 'CustProductSite.stock_code';
        $seq = !empty($this->data['Option']['seq'])? $this->data['Option']['seq']: 'ASC';

        $sql .= " GROUP BY CustProductSite.stock_code, CustStockOut.stock_out_avg_price ORDER BY {$order1} {$seq}" . ($order2 != null? ", {$order2} {$seq}": '');
        if (!empty($limit)) {
            $sql .= ' LIMIT ' . $limit;
        }

//        debug($sql);
        // sql ที่จะใช้ดึงรายการ stock out no และ remark
        $this->Session->write($this->name . 'sql', $sql_conditions);
        // set ตัวแปรให้หน้า view ว่าต้องโชว์ stock out no กับ remark หรือไม่
        $this->set('display_stock_out_no', $this->data['Option']['display_stock_out_no']);
        $this->data = $this->CustProductSite->query($sql);
    }

    /**
     * สำหรับ Stock out report กรณีมีการแสดง stock out no และ remark
     */
    function get_cust_stock_out_no_remark($cust_product_site_id, $cust_stock_out_price) {
        $sql = "SELECT CustStockOutHead.id, stock_out_no, remark, stock_out_qty, return_qty, net_stock_out_qty, DATE_FORMAT(stock_out_date, '%d %M %Y') stock_out_date
                FROM pre_cust_products_sites CustProductSite JOIN pre_products Product ON (CustProductSite.product_id = Product.id)
                JOIN pre_suppliers Supplier ON (CustProductSite.supplier_id = Supplier.id)
                JOIN pre_categories Category ON (Category.id = Product.category_id)
                JOIN pre_cust_stocks_out CustStockOut ON (CustStockOut.cust_product_site_id = CustProductSite.id)
                JOIN pre_cust_stock_out_heads CustStockOutHead ON (CustStockOut.cust_stock_out_head_id = CustStockOutHead.id)
                WHERE CustStockOut.stock_out_avg_price = {$cust_stock_out_price}
                AND CustProductSite.id = {$cust_product_site_id} ";
//                AND CustStockOut.net_stock_out_qty > 0

        // เอาเงื่อนไขจากการ search มาใส่เพื่ม
        $sql_condition = $this->Session->read($this->name . 'sql');
        $sql .= $sql_condition;
//        debug($sql);
        $data = $this->CustProductSite->query($sql);
//        debug($data);
        return $data;
    }

    function cust_stock_out_print() {
        $this->viewPath = 'its/reports/customer_reports/stock_out';

        if (!empty($this->data['Product']['category_id'])) {
            $this->CustProductSite->Product->Category->id = $this->data['Product']['category_id'];
            $this->set('category_name', $this->CustProductSite->Product->Category->field('name'));
        }

        if (!empty($this->data['CustProductSite']['customer_id'])) {
            $this->CustProductSite->Customer->id = $this->data['CustProductSite']['customer_id'];
            $this->set('customer_name', $this->CustProductSite->Customer->field('name'));
        }

        if (!empty($this->data['CustProductSite']['site_id'])) {
            $this->CustProductSite->Site->id = $this->data['CustProductSite']['site_id'];
            $this->set('site_name', $this->CustProductSite->Site->field('name'));
        }

        if (!empty($this->data['CustProductSite']['supplier_id'])) {
            $this->CustProductSite->Supplier->id = $this->data['CustProductSite']['supplier_id'];
            $this->set('supplier_name', $this->CustProductSite->Supplier->field('name'));
        }

        if (!empty($this->data['Product']['brand_id'])) {
            $this->CustProductSite->Product->Brand->id = $this->data['Product']['brand_id'];
            $this->set('brand_name', $this->CustProductSite->Product->Brand->field('name'));
        }
        
        if (!empty($this->data['CustStockOutHead']['dispatch_from'])) {
            if ($this->data['CustStockOutHead']['dispatch_from'] == ITS_PREFIX) {
                $this->set('vendor', $this->requestAction('system_preferences/get/company_name'));
            } else {
                if ($this->data['CustStockOutHead']['supplier_id'] != null) {
                    $this->Supplier->id = $this->data['CustStockOutHead']['supplier_id'];
                    $this->set('vendor', $this->Supplier->field('name'));
                } else {
                    $this->set('vendor', 'Other Vendor');
                }
            }
        }

        $this->_setReportPrintNote();
        $this->setCustStockOutOrderOptions();

        $this->cust_stock_out_query();
    }

    function cust_stock_out_excel() {
        $this->cust_stock_out_print();
        parent::excel('VMI ITS Customer Stock Out Report');
    }
}
?>